const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');

async function createDatabase() {
    let connection;
    try {
        // 创建初始连接
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: '123456'
        });

        // 创建数据库
        await connection.query('DROP DATABASE IF EXISTS cloud_drive');
        await connection.query('CREATE DATABASE cloud_drive');
        await connection.query('USE cloud_drive');

        // 创建用户表
        await connection.query(`
            CREATE TABLE users (
                id INT PRIMARY KEY AUTO_INCREMENT,
                username VARCHAR(50) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                email VARCHAR(100) NOT NULL UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);

        // 创建文件夹表
        await connection.query(`
            CREATE TABLE folders (
                id INT PRIMARY KEY AUTO_INCREMENT,
                folder_name VARCHAR(255) NOT NULL,
                parent_id INT,
                user_id INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (parent_id) REFERENCES folders(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        `);

        // 创建文件表
        await connection.query(`
            CREATE TABLE files (
                id INT PRIMARY KEY AUTO_INCREMENT,
                filename VARCHAR(255) NOT NULL,
                file_path VARCHAR(255) NOT NULL,
                file_size BIGINT NOT NULL,
                file_type VARCHAR(50),
                user_id INT NOT NULL,
                folder_id INT,
                upload_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id),
                FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
            )
        `);

        // 创建分享表
        await connection.query(`
            CREATE TABLE shared_files (
                id INT PRIMARY KEY AUTO_INCREMENT,
                file_id INT NOT NULL,
                shared_by INT NOT NULL,
                shared_with INT NOT NULL,
                share_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE,
                FOREIGN KEY (shared_by) REFERENCES users(id),
                FOREIGN KEY (shared_with) REFERENCES users(id)
            )
        `);

        // 创建测试用户
        const hashedPassword = await bcrypt.hash('123456', 10);
        const [userResult] = await connection.query(
            'INSERT INTO users (username, password, email) VALUES (?, ?, ?)',
            ['testuser', hashedPassword, 'test@example.com']
        );

        const userId = userResult.insertId;

        // 为测试用户创建默认文件夹
        const defaultFolders = [
            '我的文档',
            '我的图片',
            '我的视频',
            '我的音乐'
        ];

        for (const folderName of defaultFolders) {
            await connection.query(
                'INSERT INTO folders (folder_name, parent_id, user_id) VALUES (?, NULL, ?)',
                [folderName, userId]
            );
        }

        console.log('数据库、表、测试用户和默认文件夹创建成功！');

    } catch (error) {
        console.error('操作失败：', error);
    } finally {
        if (connection) {
            await connection.end();
        }
    }
}

createDatabase();